package com.engine.kq.cmd.report;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.engine.common.biz.AbstractCommonCommand;
import com.engine.common.entity.BizLogContext;
import com.engine.core.interceptor.CommandContext;
import com.engine.kq.biz.*;
import com.engine.kq.enums.KqSplitFlowTypeEnum;
import com.engine.qz.util.QzUtil;
import com.weaver.file.Prop;
import com.engine.kq.util.KQDurationCalculatorUtil;
import com.engine.kq.util.PageUidFactory;
import com.google.common.collect.Maps;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;
import weaver.common.DateUtil;
import weaver.conn.RecordSet;
import weaver.general.TimeUtil;
import weaver.general.Util;
import weaver.hrm.User;
import weaver.hrm.company.DepartmentComInfo;
import weaver.hrm.company.SubCompanyComInfo;
import weaver.hrm.job.JobTitlesComInfo;
import weaver.hrm.report.schedulediff.HrmScheduleDiffUtil;
import weaver.hrm.resource.ResourceComInfo;
import weaver.interfaces.qz.job.kqCustomCornJob;
import weaver.systeminfo.SystemEnv;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class GetKQReportCmd extends AbstractCommonCommand<Map<String, Object>> {

    public GetKQReportCmd(Map<String, Object> params, User user) {
        this.user = user;
        this.params = params;
    }

    @Override
    public Map<String, Object> execute(CommandContext commandContext) {
        Map<String, Object> retmap = new HashMap<String, Object>();
        RecordSet rs = new RecordSet();
        String sql = "";
        writeLog("GetKQReportCmd start.");
        try {
            String pageUid = PageUidFactory.getHrmPageUid("KQReport");

            SubCompanyComInfo subCompanyComInfo = new SubCompanyComInfo();
            DepartmentComInfo departmentComInfo = new DepartmentComInfo();
            ResourceComInfo resourceComInfo = new ResourceComInfo();
            JobTitlesComInfo jobTitlesComInfo = new JobTitlesComInfo();
            KQLeaveRulesBiz kqLeaveRulesBiz = new KQLeaveRulesBiz();
            KQReportBiz kqReportBiz = new KQReportBiz();
            KQSettingsComInfo kqSettingsComInfo = new KQSettingsComInfo();
            String leavetype_is_on = Util.null2String(kqSettingsComInfo.getMain_val("leavetype_is_on"), "0");

            JSONObject jsonObj = JSON.parseObject(Util.null2String(params.get("data")));
            String attendanceSerial = Util.null2String(jsonObj.get("attendanceSerial"));
            String fromDate = Util.null2String(jsonObj.get("fromDate"));
            String toDate = Util.null2String(jsonObj.get("toDate"));
            String typeselect = Util.null2String(jsonObj.get("typeselect"));
            if (typeselect.length() == 0) typeselect = "3";
            if (!typeselect.equals("") && !typeselect.equals("0") && !typeselect.equals("6")) {
                if (typeselect.equals("1")) {
                    fromDate = TimeUtil.getCurrentDateString();
                    toDate = TimeUtil.getCurrentDateString();
                } else {
                    fromDate = TimeUtil.getDateByOption(typeselect, "0");
                    toDate = TimeUtil.getDateByOption(typeselect, "1");
                }
            }
            //人员状态
            String status = Util.null2String(jsonObj.get("status"));
            String subCompanyId = Util.null2String(jsonObj.get("subCompanyId"));
            String departmentId = Util.null2String(jsonObj.get("departmentId"));
            String resourceId = Util.null2String(jsonObj.get("resourceId"));
            String allLevel = Util.null2String(jsonObj.get("allLevel"));
            String isNoAccount = Util.null2String(jsonObj.get("isNoAccount"));
            String viewScope = Util.null2String(jsonObj.get("viewScope"));
            String isFromMyAttendance = Util.null2String(jsonObj.get("isFromMyAttendance"));//是否是来自我的考勤的请求，如果是，不加载考勤报表权限共享的限制，不然我的考勤会提示无权限
            int pageIndex = Util.getIntValue(Util.null2String(jsonObj.get("pageIndex")), 1);
            int pageSize = KQReportBiz.getPageSize(Util.null2String(jsonObj.get("pageSize")), pageUid, user.getUID());
            boolean isKqReportHalfOpen = "1".equals(new KQSettingsComInfo().getMain_val("kq_report_half"));
            if (isKqReportHalfOpen) {
                pageSize = pageSize / 2;
            }
            int count = 0;
            int pageCount = 0;
            int isHavePre = 0;
            int isHaveNext = 0;


            String rightSql = kqReportBiz.getReportRight("1", "" + user.getUID(), "a");
            if (isFromMyAttendance.equals("1")) {
                rightSql = "";
            }

            List<Map<String, Object>> leaveRules = kqLeaveRulesBiz.getAllLeaveRules();
            List<Object> columns = new ArrayList();
            Map<String, Object> column = null;
            List<Object> datas = new ArrayList();
            Map<String, Object> data = null;
            Map<String, Object> mapChildColumnInfo = null;
            List<Object> childColumns = null;
            KQReportFieldComInfo kqReportFieldComInfo = new KQReportFieldComInfo();
            while (kqReportFieldComInfo.next()) {
                if (Util.null2String(kqReportFieldComInfo.getParentid()).length() > 0) continue;
                if (kqReportFieldComInfo.getFieldname().equals("kqCalendar")) continue;
                if (KQReportFieldComInfo.cascadekey2fieldname.keySet().contains(kqReportFieldComInfo.getFieldname()))
                    continue;
                if (!kqReportFieldComInfo.getReportType().equals("all") && !kqReportFieldComInfo.getReportType().equals("month"))
                    continue;
                if ("leave".equalsIgnoreCase(kqReportFieldComInfo.getFieldname()) && leaveRules.size() == 0) continue;

                column = new HashMap();
                column.put("title", SystemEnv.getHtmlLabelNames(kqReportFieldComInfo.getFieldlabel(), user.getLanguage()));
                column.put("unit", KQReportBiz.getUnitType(kqReportFieldComInfo, user));
                column.put("dataIndex", kqReportFieldComInfo.getFieldname());
                column.put("type", kqReportFieldComInfo.getFieldname());
                column.put("key", kqReportFieldComInfo.getFieldname());
                column.put("isSystem", kqReportFieldComInfo.getIsSystem());
                mapChildColumnInfo = this.getChildColumnsInfo(kqReportFieldComInfo.getFieldname(), user);
                childColumns = (List<Object>) mapChildColumnInfo.get("childColumns");
                if (childColumns.size() > 0) {//跨列width取子列的width
                    column.put("rowSpan", 1);
                    column.put("width", mapChildColumnInfo.get("sumChildColumnWidth"));
                    column.put("children", childColumns);
                } else {
                    column.put("rowSpan", 3);
                    column.put("width", Util.getIntValue(kqReportFieldComInfo.getWidth()));
                }
                column.put("showDetial", kqReportFieldComInfo.getShowDetial());
                columns.add(column);
                columns.addAll(this.getCascadeKeyColumnsInfo(kqReportFieldComInfo.getCascadekey(), user));
            }

            boolean isEnd = false;
            Calendar cal = DateUtil.getCalendar();
            String today = DateUtil.getCurrentDate();
//			if(DateUtil.compDate(today, toDate)>0){//结束日期不大于今天
//				toDate = today;
//				if(DateUtil.compDate(today, fromDate)>0){//结束日期不大于今天
//          fromDate = today;
//        }
//			}

            childColumns = new ArrayList<>();
            for (String date = fromDate; !isEnd; ) {
                if (date.equals(toDate)) isEnd = true;
                column = new HashMap();
                column.put("title", DateUtil.geDayOfMonth(date));
                column.put("dataIndex", date);
                column.put("key", date);
                column.put("type", date);
                column.put("rowSpan", 1);
                column.put("width", 90);
                column.put("isCalendar", 1);
                childColumns.add(column);
                cal.setTime(DateUtil.parseToDate(date));
                date = DateUtil.getDate(cal.getTime(), 1);
            }

            column = new HashMap();
            column.put("title", SystemEnv.getHtmlLabelName(386476, user.getLanguage()));
            column.put("dataIndex", "kqCalendar");
            column.put("key", "kqCalendar");
            if (childColumns.size() > 0) {//跨列width取子列的width
                column.put("rowSpan", 1);
                column.put("width", childColumns.size() * 90);
                column.put("children", childColumns);
            }
            columns.add(column);


            // 没有权限的人最大只支持查询本季
            Map<String, String> getRealDate = new KQReportBiz().realDate(fromDate, toDate, user, "1");
            fromDate = getRealDate.get("fromDate");
            toDate = getRealDate.get("toDate");

            String forgotBeginWorkCheck_field = " sum(b.forgotBeginWorkCheck) ";

            if (rs.getDBType().equalsIgnoreCase("oracle")) {
                forgotBeginWorkCheck_field = " sum(nvl(b.forgotBeginWorkCheck,0))  ";
            } else if ((rs.getDBType()).equalsIgnoreCase("mysql")) {
                forgotBeginWorkCheck_field = " sum(ifnull(b.forgotBeginWorkCheck,0)) ";
            } else {
                forgotBeginWorkCheck_field = " sum(isnull(b.forgotBeginWorkCheck,0)) ";
            }

            Map<String, Object> definedFieldInfo = new KQFormatBiz().getDefinedField();
            String definedFieldSum = Util.null2String(definedFieldInfo.get("definedFieldSum"));
            //钱智二开，区间内工作日天数 区间内天数-周末-调配休息日（排除周末）-公众节假日(排除周末)+调配日工作日（排除非周末的标签）
            //先去生成考勤组在期间内对应的工作日天数
            generateWorkDays(fromDate, toDate, rs);
            String backFields = " sum(zm.sjycqts) as sjycqts, sum(zm.sjycqss) as sjycqss, sum(zm.sjdksc) as sjdksc,bb.kgtsqz,tt.workdaysbymonth," +
                    " a.id,a.lastname,a.workcode,a.dsporder,b.resourceid,a.subcompanyid1 as subcompanyid,a.departmentid,a.jobtitle," +
                    " ufd.tname," +
                    " sum(b.workdays) as workdays,sum(b.workMins) as workMins,sum(b.attendancedays) as attendancedays," +
                    " sum(b.attendanceMins) as attendanceMins,sum(b.beLate) as beLate,sum(b.beLateMins) as beLateMins, " +
                    " sum(b.graveBeLate) as graveBeLate, sum(b.graveBeLateMins) as graveBeLateMins,sum(b.leaveEearly) as leaveEearly," +
                    " sum(b.leaveEarlyMins) as leaveEarlyMins, sum(b.graveLeaveEarly) as graveLeaveEarly, " +
                    " sum(b.graveLeaveEarlyMins) as graveLeaveEarlyMins,sum(b.absenteeism) as absenteeism, " +
                    " sum(b.signdays) as signdays,sum(b.signmins) as signmins, " +
                    " sum(b.absenteeismMins) as absenteeismMins," +
                    " sum(b.forgotCheck)+" + forgotBeginWorkCheck_field + " as forgotCheck " + (definedFieldSum.length() > 0 ? "," + definedFieldSum + "" : "");

            if (rs.getDBType().equals("oracle")) {
                backFields = "/*+ index(kq_format_total IDX_KQ_FORMAT_TOTAL_KQDATE) */ " + backFields;
            }
            String sqlFrom = " from hrmresource a " +
                    "JOIN kq_format_total b ON a.id= b.resourceid " +
                    "LEFT JOIN uf_custom_holiday tt on tt.groupid=b.groupid \n" +
                    "LEFT JOIN uf_kqsjcqjsglb zm ON zm.xm = a.id AND zm.rq = b.kqdate " +
                    "left join uf_first_dept ufd on ufd.deptid=a.departmentid" +
                    " left join (\n" +
                    "select resourceid,sum(a.kgtsqz)as kgtsqz  from (select  resourceid,CASE  WHEN absenteeismMins IS NULL THEN 0\n" +
                    "             WHEN absenteeismMins = 0 THEN 0\n" +
                    "             WHEN absenteeismMins / 60.0 <= 3 THEN 0.5\n" +
                    "              ELSE  1\n" +
                    "               END AS kgtsqz  from kq_format_total\n" +
                    "   where  kqdate>='" + fromDate + "' and kqdate<='" + toDate + "' ) a \n" +
                    "  group by resourceid ) bb on bb.resourceid= a.id " +
                    " WHERE b.kqdate >='" + fromDate + "' and b.kqdate <='" + toDate + "'";
            String sqlWhere = rightSql;
            String groupBy = " group by a.id,a.lastname,a.workcode,a.dsporder,b.resourceid,a.subcompanyid1,a.departmentid,a.jobtitle,ufd.tname,bb.kgtsqz,tt.workdaysbymonth ";
            List<List> paramLists = new ArrayList<>();
            List<Object> paramList = null;
            if (subCompanyId.length() > 0) {
                sqlWhere += " and a.subcompanyid1 in(" + KQReportBiz.splitParamStr(subCompanyId) + ") ";
                for (String bean : subCompanyId.split(",")) {
                    paramList = new ArrayList<>();
                    paramList.add(bean);
                    paramLists.add(paramList);
                }
            }

            if (departmentId.length() > 0) {
                sqlWhere += " and a.departmentid in(" + KQReportBiz.splitParamStr(departmentId) + ") ";
                for (String bean : departmentId.split(",")) {
                    paramList = new ArrayList<>();
                    paramList.add(bean);
                    paramLists.add(paramList);
                }
            }

            if (resourceId.length() > 0) {
                sqlWhere += " and a.id in(" + KQReportBiz.splitParamStr(resourceId) + ") ";
                for (String bean : resourceId.split(",")) {
                    paramList = new ArrayList<>();
                    paramList.add(bean);
                    paramLists.add(paramList);
                }
            }


            if (viewScope.equals("4")) {//我的下属
                if (allLevel.equals("1")) {//所有下属
                    sqlWhere += " and a.managerstr like '%," + user.getUID() + ",%'";
                } else {
                    sqlWhere += " and a.managerid=" + user.getUID();//直接下属
                }
            }
            if (!"1".equals(isNoAccount)) {
                sqlWhere += " and a.loginid is not null " + (rs.getDBType().equals("oracle") ? "" : " and a.loginid<>'' ");
            }

            if (status.length() > 0) {
                if (!status.equals("8") && !status.equals("9")) {
                    sqlWhere += " and a.status = ?";
                    paramList = new ArrayList<>();
                    paramList.add(status);
                    paramLists.add(paramList);
                } else if (status.equals("8")) {
                    sqlWhere += " and (a.status = 0 or a.status = 1 or a.status = 2 or a.status = 3) ";
                }
            }
            if ("-1".equals(fromDate) || "-1".equals(toDate)) {
                sqlWhere += " and 1=2 ";
            }
            sql = " select count(*) as c from ( select 1 as c " + sqlFrom + sqlWhere + groupBy + ") t";
            rs.executeQuery(sql, paramLists);
            if (rs.next()) {
                count = rs.getInt("c");
            }

            if (count <= 0) {
                pageCount = 0;
            }

            pageCount = count / pageSize + ((count % pageSize > 0) ? 1 : 0);

            isHaveNext = (pageIndex + 1 <= pageCount) ? 1 : 0;

            isHavePre = (pageIndex - 1 >= 1) ? 1 : 0;

            String orderBy = " order by t.dsporder asc, t.lastname asc ";
            String descOrderBy = " order by t.dsporder desc, t.lastname desc ";

            //默认排序设置 start，有性能问题，先取消，后面再看看有没有好的方式
//			String orderBySql = "select * from kq_report_order where userId=? and sort=1 order by orders";
//			rs.executeQuery(orderBySql, user.getUID());
//			if (rs.getCounts() <= 0) {
//				orderBySql = "select * from kq_report_order where userId=0 and sort=1 order by orders";
//				rs.executeQuery(orderBySql);
//			}
//			while (rs.next()) {
//				String dataIndex = rs.getString("dataIndex");
//				String ascOrDesc = rs.getString("ascOrDesc");
//				String ascOrDesc1 = (ascOrDesc.equals("")||ascOrDesc.equals("asc"))?"desc":"asc";
//				if (dataIndex.equals("organization")) {
//					orderBy += ",showOrderOfDeptTree " + ascOrDesc + ",dept_id " + ascOrDesc;
//					descOrderBy += ",showOrderOfDeptTree " + ascOrDesc1 + ",dept_id " + ascOrDesc1;
//				}
//				if (dataIndex.equalsIgnoreCase("dspOrder") || dataIndex.equalsIgnoreCase("lastName")) {
//					orderBy += "," + dataIndex + " " + ascOrDesc + ",id " + ascOrDesc;
//					descOrderBy += "," + dataIndex + " " + ascOrDesc1 + ",id " + ascOrDesc1;
//				} else if (dataIndex.equalsIgnoreCase("deptShowOrder") || dataIndex.equalsIgnoreCase("deptName")) {
//					orderBy += "," + dataIndex + " " + ascOrDesc + ",dept_id " + ascOrDesc;
//					descOrderBy += "," + dataIndex + " " + ascOrDesc1 + ",dept_id " + ascOrDesc1;
//				} else if (dataIndex.equalsIgnoreCase("subcomShowOrder") || dataIndex.equalsIgnoreCase("subcomName")) {
//					orderBy += "," + dataIndex + " " + ascOrDesc + ",subcom_id " + ascOrDesc;
//					descOrderBy += "," + dataIndex + " " + ascOrDesc1 + ",subcom_id " + ascOrDesc1;
//				}
//			}
//			orderBy = orderBy.startsWith(",") ? orderBy.substring(1) : orderBy;
//			descOrderBy = descOrderBy.startsWith(",") ? descOrderBy.substring(1) : descOrderBy;
//			orderBy = orderBy.equals("") ? " t.dspOrder,t.id " : orderBy;
//			descOrderBy = descOrderBy.equals("") ? " t.dspOrder,t.id " : descOrderBy;
//			orderBy = "order by "+orderBy;

            sql = backFields + sqlFrom + sqlWhere + groupBy;

            if (pageIndex > 0 && pageSize > 0) {
                if (rs.getDBType().equals("oracle")) {
                    sql = " select * from (select " + sql + ") t " + orderBy;
                    sql = "select * from ( select row_.*, rownum rownum_ from ( " + sql + " ) row_ where rownum <= "
                            + (pageIndex * pageSize) + ") where rownum_ > " + ((pageIndex - 1) * pageSize);
                } else if (rs.getDBType().equals("mysql")) {
                    sql = " select * from (select " + sql + ") t " + orderBy;
                    sql = "select t1.* from (" + sql + ") t1 limit " + ((pageIndex - 1) * pageSize) + "," + pageSize;
                } else if (rs.getDBType().equals("postgresql")) {
                    sql = " select * from (select " + sql + ") t " + orderBy;
                    sql = "select t1.* from (" + sql + ") t1 limit " + pageSize + " offset " + ((pageIndex - 1) * pageSize);
                } else {
                    orderBy = " order by dsporder asc, lastname asc ";
                    descOrderBy = " order by dsporder desc, lastname desc ";
                    if (pageIndex > 1) {
                        int topSize = pageSize;
                        if (pageSize * pageIndex > count) {
                            topSize = count - (pageSize * (pageIndex - 1));
                        }
                        sql = " select top " + topSize + " * from ( select top  " + topSize + " * from ( select top "
                                + (pageIndex * pageSize) + sql + orderBy + " ) tbltemp1 " + descOrderBy + ") tbltemp2 " + orderBy;
                    } else {
                        sql = " select top " + pageSize + sql + orderBy;
                    }
                }
            } else {
                sql = " select " + sql;
            }


            // #1475814-概述：满足考勤报分部部门显示及导出时显示全路径
            String fullPathMainKey = "show_full_path";
            String isShowFullPath = Util.null2String(kqSettingsComInfo.getMain_val(fullPathMainKey), "0");

            String show_card_source = Util.null2String(kqSettingsComInfo.getMain_val("show_card_source"), "0");//是否显示打卡数据，以及打卡数据来源
            params.put("show_card_source", show_card_source);
            KQOvertimeRulesBiz kqOvertimeRulesBiz = new KQOvertimeRulesBiz();
            int uintType = kqOvertimeRulesBiz.getMinimumUnit();//当前加班单位
            double hoursToDay = kqOvertimeRulesBiz.getHoursToDay();//当前天跟小时计算关系
            params.put("uintType", uintType);
            params.put("hoursToDay", hoursToDay);
            Map<String, Object> flowData = kqReportBiz.getFlowData(params, user);

            RecordSet rsTemp = new RecordSet();
            Map<String, String> workdaysMap = new HashMap<>();
            rsTemp.execute("SELECT resourceid, sum(workdays) as wk from kq_format_total where kqdate >='" + fromDate + "' and kqdate <='" + toDate + "' group by resourceid ");
            while (rsTemp.next()) {
                String xm = rsTemp.getString("resourceid");
                String wk = rsTemp.getString("wk");
                if (StringUtils.isEmpty(xm)) {
                    continue;
                }
                if (StringUtils.isEmpty(wk)) {
                    wk = "0";
                }
                workdaysMap.put(xm, wk);
            }
            Map<String, String> ypjcqscMap = new HashMap<>();
            Map<String, String> sjycqtsMap = new HashMap<>();
            Map<String, String> sjdkscMap = new HashMap<>();
            rsTemp.execute("select xm, sum(sjycqts) as ts,sum(sjycqss) as ss, sum(sjdksc) as sjdksc from uf_kqsjcqjsglb where rq >= '" + fromDate + "' and rq <= '" + toDate + "' group by xm");
            while (rsTemp.next()) {
                String xm = rsTemp.getString("xm");
                String ts = rsTemp.getString("ts");
                String ss = rsTemp.getString("ss");
                String sjdksc = rsTemp.getString("sjdksc");
                if (StringUtils.isEmpty(ts)) {
                    ts = "0";
                }
                if (StringUtils.isEmpty(ss)) {
                    ss = "0";
                }
                if (StringUtils.isEmpty(sjdksc)) {
                    sjdksc = "0";
                }
                sjycqtsMap.put(xm, ts);
                String wk = workdaysMap.get(xm);
                if (StringUtils.isEmpty(wk) || new BigDecimal(wk).compareTo(BigDecimal.ZERO) == 0) {
                    sjdkscMap.put(xm, "0");
                } else {
                    BigDecimal wkValue = new BigDecimal(sjdksc).divide(new BigDecimal(wk), 2, RoundingMode.HALF_UP);
                    if (wkValue.compareTo(BigDecimal.ZERO) == 0) {
                        sjdkscMap.put(xm, "0");
                    } else {
                        sjdkscMap.put(xm, wkValue.toString());
                    }
                }

                if (new BigDecimal(ts).compareTo(new BigDecimal("10")) < 0) {
                    ypjcqscMap.put(xm, "0");
                } else {
                    BigDecimal fieldValue = new BigDecimal(ss).divide(new BigDecimal(ts), 2, RoundingMode.HALF_UP);
                    if (fieldValue.compareTo(BigDecimal.ZERO) == 0) {
                        ypjcqscMap.put(xm, "0");
                    } else {
                        ypjcqscMap.put(xm, fieldValue.toString());
                    }
                }
            }
            // 获取公休日
            Map<String, Set<String>> groupIdRqMap = new HashMap<>();
            rsTemp.execute("select groupId, holidayDate from kq_HolidaySet where changeType = 1 and holidayDate>='" + fromDate + "' and holidayDate<='" + toDate + "'");
            while (rsTemp.next()) {
                String groupIdTemp = rsTemp.getString("groupId");
                String holidayDate = rsTemp.getString("holidayDate");
                if (StringUtils.isEmpty(holidayDate) || StringUtils.isEmpty(groupIdTemp)) {
                    continue;
                }
                Set<String> dateSet = new HashSet<>();
                if ((!CollectionUtils.isEmpty(groupIdRqMap)) && groupIdRqMap.containsKey(groupIdTemp)) {
                    dateSet = groupIdRqMap.get(groupIdTemp);
                }
                dateSet.add(holidayDate);
                groupIdRqMap.put(groupIdTemp, dateSet);
            }
            // 获取补卡次数  （补卡次数逻辑调整 2025-08-05）
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH:mm:ss");
            Map<String, Set<String>> bdkSetMap = new HashMap<>();
//			rsTemp.execute("select userid, signdate, signtime from hrmschedulesign where signfrom like 'card%' and signfrom != 'card'" +
//					" and signdate >= '" + fromDate + "' and signdate <= '" + toDate + "'");
            String bkSql = "select a.* from \n" +
                    "(SELECT a1.yg as userid, a1.kqrq as signdate, a1.bksj as signtime, a2.requestId FROM formtable_main_40_dt1 a1, formtable_main_40 a2 WHERE a1.mainid = a2.id) a,\n" +
                    "workflow_requestbase b \n" +
                    "WHERE a.requestId = b.requestId and b.currentnodetype = 3 \n" +
                    "and a.signdate >= '" + fromDate + "' and a.signdate <= '" + toDate + "'";
            rsTemp.execute(bkSql);
            while (rsTemp.next()) {
                String id = rsTemp.getString("userid");
                String signdate = rsTemp.getString("signdate");
                String signtime = rsTemp.getString("signtime");
                if (StringUtils.isEmpty(id) || StringUtils.isEmpty(signdate) || StringUtils.isEmpty(signtime)) {
                    continue;
                }
                if (signtime.length() == 5) {
                    signtime = signtime + ":00";
                }
                String keyTemp = id + "_" + signdate;
                Set<String> bdkSet = new HashSet<>();
                if ((!CollectionUtils.isEmpty(bdkSetMap)) && bdkSetMap.containsKey(keyTemp)) {
                    bdkSet = bdkSetMap.get(keyTemp);
                }
                LocalTime time = LocalTime.parse(signtime, formatter);
                int hour = time.getHour(); // 提取小时
                bdkSet.add(String.valueOf(hour));
                bdkSetMap.put(keyTemp, bdkSet);
            }
            Map<String, Integer> bdkMap = new HashMap<>();
            for (Map.Entry<String, Set<String>> entry : bdkSetMap.entrySet()) {
                String key = entry.getKey();
                if (StringUtils.isEmpty(key)) {
                    continue;
                }
                String ryId = key.split("_")[0];
                int size = 0;
                if ((!CollectionUtils.isEmpty(bdkMap)) && bdkMap.containsKey(ryId)) {
                    size = bdkMap.get(ryId);
                }
                Set<String> valueSet = entry.getValue();
                if (!CollectionUtils.isEmpty(valueSet)) {
                    if (valueSet.size() <= 2) {
                        size = size + valueSet.size();
                    } else {
                        size = size + 2;
                    }
                }
                bdkMap.put(ryId, size);
            }

            // 获取所有离职人员对应最新日期
            Map<String, String> lzrqMap = new HashMap<>();
            rs.execute("SELECT yg, Max(shenxrq) AS ddrq FROM uf_rzjl where bdsj = 3 GROUP BY yg");
            while (rs.next()) {
                String yg = rs.getString("yg");
                String ddrq = rs.getString("ddrq");
                if (StringUtils.isEmpty(yg) || StringUtils.isEmpty(ddrq)) {
                    continue;
                }
                lzrqMap.put(yg, ddrq);
            }

            // 获取所有人员当前对应状态
            Map<String, String> ryZtMap = new HashMap<>();
            Map<String, String> ryRzrqMap = new HashMap<>();
            rs.execute("SELECT id, status, companystartdate FROM hrmresource");
            while (rs.next()) {
                String id = rs.getString("id");
                String zt = rs.getString("status");
                String companystartdate = rs.getString("companystartdate");
                if (StringUtils.isEmpty(id)) {
                    continue;
                }
                ryZtMap.put(id, zt);
                ryRzrqMap.put(id, companystartdate);
            }
            DateTimeFormatter formatterRq = DateTimeFormatter.ofPattern("yyyy-MM-dd");

            KQGroupMemberComInfo kqGroupMemberComInfo = new KQGroupMemberComInfo();

            HrmScheduleDiffUtil manager = new HrmScheduleDiffUtil();
            QzUtil qzUtil = new QzUtil();
            List<String> allDates = qzUtil.getAllDates(fromDate, toDate);

            // 获取工作日信息
            Set<String> groupIdSet = new HashSet<>();
            Map<String, Set<String>> holidaySetMap = new HashMap<>();
            Map<String, Set<String>> dpgzrdaySetMap = new HashMap<>();
            rs.executeQuery("select holidayDate,changeType,groupId from KQ_HolidaySet where holidayDate >= ? AND holidayDate <= ?", fromDate, toDate);
            while (rs.next()) {
                String holidayDate = rs.getString("holidayDate");
                String changeType = rs.getString("changeType");
                String groupIdTemp = rs.getString("groupId");
                if (StringUtils.isNotEmpty(holidayDate) && StringUtils.isNotEmpty(changeType) && StringUtils.isNotEmpty(groupIdTemp)) {
                    BigDecimal changeTypeBd = new BigDecimal(changeType);
                    groupIdSet.add(groupIdTemp);
                    if (changeTypeBd.compareTo(new BigDecimal("1")) == 0 || changeTypeBd.compareTo(new BigDecimal("3")) == 0) {
                        Set<String> holidaySet = new HashSet<>();
                        if ((!CollectionUtils.isEmpty(holidaySetMap)) && holidaySetMap.containsKey(groupIdTemp)) {
                            holidaySet = holidaySetMap.get(groupIdTemp);
                        }
                        holidaySet.add(holidayDate);
                        holidaySetMap.put(groupIdTemp, holidaySet);
                    }
                    if (changeTypeBd.compareTo(new BigDecimal("2")) == 0) {
                        Set<String> dpgzrdaySet = new HashSet<>();
                        if ((!CollectionUtils.isEmpty(dpgzrdaySetMap)) && dpgzrdaySetMap.containsKey(groupIdTemp)) {
                            dpgzrdaySet = dpgzrdaySetMap.get(groupIdTemp);
                        }
                        dpgzrdaySet.add(holidayDate);
                        dpgzrdaySetMap.put(groupIdTemp, dpgzrdaySet);
                    }
                }
            }
            rs.executeQuery(sql, paramLists);
            while (rs.next()) {
                data = new HashMap<>();
                kqReportFieldComInfo.setTofirstRow();
                String id = rs.getString("id");
                data.put("resourceId", id);
                while (kqReportFieldComInfo.next()) {
                    if (!Util.null2String(kqReportFieldComInfo.getIsdataColumn()).equals("1")) continue;
                    if (!kqReportFieldComInfo.getReportType().equals("all") && !kqReportFieldComInfo.getReportType().equals("month"))
                        continue;
                    if ("leave".equalsIgnoreCase(kqReportFieldComInfo.getFieldname()) && leaveRules.size() == 0) {
                        continue;
                    }
                    String fieldName = kqReportFieldComInfo.getFieldname();
                    String fieldValue = "";
                    if (fieldName.equals("subcompany")) {
                        String tmpSubcompanyId = Util.null2String(rs.getString("subcompanyid"));
                        if (tmpSubcompanyId.length() == 0) {
                            tmpSubcompanyId = Util.null2String(resourceComInfo.getSubCompanyID(id));
                        }
                        data.put("subcompanyId", tmpSubcompanyId);

                        fieldValue = "1".equals(isShowFullPath) ?
                                SubCompanyComInfo.getSubcompanyRealPath(tmpSubcompanyId, "/", "0") :
                                subCompanyComInfo.getSubCompanyname(tmpSubcompanyId);

                        // fieldValue = subCompanyComInfo.getSubCompanyname(tmpSubcompanyId);
                    } else if (fieldName.equals("department")) {
                        String tmpDepartmentId = Util.null2String(rs.getString("departmentid"));
                        if (tmpDepartmentId.length() == 0) {
                            tmpDepartmentId = Util.null2String(resourceComInfo.getDepartmentID(id));
                        }
                        data.put("departmentId", tmpDepartmentId);

                        fieldValue = "1".equals(isShowFullPath) ?
                                departmentComInfo.getDepartmentRealPath(tmpDepartmentId, "/", "0") :
                                departmentComInfo.getDepartmentname(tmpDepartmentId);

                        // fieldValue = departmentComInfo.getDepartmentname(tmpDepartmentId);
                    } else if (fieldName.equals("jobtitle")) {
                        String tmpJobtitleId = Util.null2String(rs.getString("jobtitle"));
                        if (tmpJobtitleId.length() == 0) {
                            tmpJobtitleId = Util.null2String(resourceComInfo.getJobTitle(id));
                        }
                        data.put("jobtitleId", tmpJobtitleId);
                        fieldValue = jobTitlesComInfo.getJobTitlesname(tmpJobtitleId);
                    } else if (fieldName.equals("attendanceSerial")) {
                        List<String> serialIds = null;
                        if (attendanceSerial.length() > 0) {
                            serialIds = Util.splitString2List(attendanceSerial, ",");
                        }
                        for (int i = 0; serialIds != null && i < serialIds.size(); i++) {
                            data.put(serialIds.get(i), kqReportBiz.getSerialCount(id, fromDate, toDate, serialIds.get(i)));
                        }
                    } else if (kqReportFieldComInfo.getParentid().equals("overtime") || kqReportFieldComInfo.getParentid().equals("overtime_nonleave")
                            || kqReportFieldComInfo.getParentid().equals("overtime_4leave") || fieldName.equals("businessLeave") || fieldName.equals("officialBusiness")) {
                        if (fieldName.equals("overtimeTotal")) {
                            double workingDayOvertime_4leave = Util.getDoubleValue(Util.null2String(flowData.get(id + "|workingDayOvertime_4leave")));
                            workingDayOvertime_4leave = workingDayOvertime_4leave < 0 ? 0 : workingDayOvertime_4leave;
                            double restDayOvertime_4leave = Util.getDoubleValue(Util.null2String(flowData.get(id + "|restDayOvertime_4leave")));
                            restDayOvertime_4leave = restDayOvertime_4leave < 0 ? 0 : restDayOvertime_4leave;
                            double holidayOvertime_4leave = Util.getDoubleValue(Util.null2String(flowData.get(id + "|holidayOvertime_4leave")));
                            holidayOvertime_4leave = holidayOvertime_4leave < 0 ? 0 : holidayOvertime_4leave;

                            double workingDayOvertime_nonleave = Util.getDoubleValue(Util.null2String(flowData.get(id + "|workingDayOvertime_nonleave")));
                            workingDayOvertime_nonleave = workingDayOvertime_nonleave < 0 ? 0 : workingDayOvertime_nonleave;
                            double restDayOvertime_nonleave = Util.getDoubleValue(Util.null2String(flowData.get(id + "|restDayOvertime_nonleave")));
                            restDayOvertime_nonleave = restDayOvertime_nonleave < 0 ? 0 : restDayOvertime_nonleave;
                            double holidayOvertime_nonleave = Util.getDoubleValue(Util.null2String(flowData.get(id + "|holidayOvertime_nonleave")));
                            holidayOvertime_nonleave = holidayOvertime_nonleave < 0 ? 0 : holidayOvertime_nonleave;

                            fieldValue = KQDurationCalculatorUtil.getDurationRound(String.valueOf(workingDayOvertime_4leave + restDayOvertime_4leave + holidayOvertime_4leave +
                                    workingDayOvertime_nonleave + restDayOvertime_nonleave + holidayOvertime_nonleave));
                        } else if (fieldName.equals("businessLeave") || fieldName.equals("officialBusiness")) {
                            String businessLeaveData = Util.null2s(Util.null2String(flowData.get(id + "|" + fieldName)), "0.0");
                            String backType = fieldName + "_back";
                            String businessLeavebackData = Util.null2s(Util.null2String(flowData.get(id + "|" + backType)), "0.0");
                            String businessLeave = "";
                            try {
                                //以防止出现精度问题
                                if (businessLeaveData.length() == 0) {
                                    businessLeaveData = "0.0";
                                }
                                if (businessLeavebackData.length() == 0) {
                                    businessLeavebackData = "0.0";
                                }
                                BigDecimal b_businessLeaveData = new BigDecimal(businessLeaveData);
                                BigDecimal b_businessLeavebackData = new BigDecimal(businessLeavebackData);
                                businessLeave = b_businessLeaveData.subtract(b_businessLeavebackData).toString();
                                if (Util.getDoubleValue(businessLeave, -1) < 0) {
                                    businessLeave = "0.0";
                                }
                            } catch (Exception e) {
                            }
                            fieldValue = KQDurationCalculatorUtil.getDurationRound(businessLeave);
                        } else {
                            fieldValue = KQDurationCalculatorUtil.getDurationRound(Util.null2String(flowData.get(id + "|" + fieldName)));
                        }
                    } else if (fieldName.equals("ypjcqsc")) {
                        fieldValue = ypjcqscMap.get(id);
                        if (StringUtils.isEmpty(fieldValue)) {
                            fieldValue = "0";
                        }
                        String s = sjycqtsMap.get(id);
                        if (StringUtils.isEmpty(s) || new BigDecimal(s).compareTo(new BigDecimal("10")) < 0) {
                            fieldValue = "0";
                        } else {
                            String sqltemp = " select resourceid, newleavetype, durationrule, sum(duration) as val,belongdate from hrmresource a, " + KqSplitFlowTypeEnum.LEAVE.getTablename() + " b " +
                                    " where a.id = b.resourceid and belongdate >='" + fromDate + "' and belongdate <='" + toDate + "' and b.resourceid = " + id +
                                    " group by resourceid, newleavetype, durationrule,belongdate ";
                            int qjtstemp = 0;
                            rsTemp.execute(sqltemp);
                            while (rsTemp.next()) {
                                String belongDatetemp = rsTemp.getString("belongdate");
                                if (StringUtils.isNotEmpty(belongDatetemp)) {
                                    qjtstemp = qjtstemp + 1;
                                }
                            }
                            if (qjtstemp > 10) {
                                fieldValue = "0";
                            }
                        }
                    } else if (fieldName.equals("ypjdksc")) {
                        fieldValue = sjdkscMap.get(id);
                        if (StringUtils.isEmpty(fieldValue)) {
                            fieldValue = "0";
                        }
                    } else if (fieldName.equals("dygzrts")) {  // 当月工作日天数
//						User user = new User(Integer.parseInt(id));
//						manager.setUser(user);
//						int cnt = 0;
//						for (String dateTemp : allDates) {
//							if (manager.getIsWorkday(dateTemp)) {
//								cnt = cnt + 1;
//							}
//						}
                        int cnt = 0;
                        for (String rqTemp : allDates) {
                            String groupId = kqGroupMemberComInfo.getKQGroupId(id, rqTemp);
                            if (StringUtils.isEmpty(groupId)) {
                                continue;
                            }
                            if (!CollectionUtils.isEmpty(groupIdSet)) {
                                Set<String> holidaySet = holidaySetMap.get(groupId);
                                Set<String> dpgzrdaySet = dpgzrdaySetMap.get(groupId);
                                if (CollectionUtils.isEmpty(holidaySet) || !holidaySet.contains(rqTemp)) {
                                    if ((!CollectionUtils.isEmpty(dpgzrdaySet)) && dpgzrdaySet.contains(rqTemp)) {
                                        cnt = cnt + 1;
                                    } else {
                                        DayOfWeek dayOfWeek = LocalDate.parse(rqTemp).getDayOfWeek();
                                        if (dayOfWeek != DayOfWeek.SATURDAY && dayOfWeek != DayOfWeek.SUNDAY) {
                                            cnt = cnt + 1;
                                        }
                                    }
                                }
                            } else {
                                DayOfWeek dayOfWeek = LocalDate.parse(rqTemp).getDayOfWeek();
                                if (dayOfWeek != DayOfWeek.SATURDAY && dayOfWeek != DayOfWeek.SUNDAY) {
                                    cnt = cnt + 1;
                                }
                            }
                        }
                        fieldValue = String.valueOf(cnt);
                    } else if (fieldName.equals("dyfdjrts")) {  // 当月法定假日天数
                        int cnt = 0;
                        String ryRzrq = ryRzrqMap.get(id);
                        String ryZt = ryZtMap.get(id);
                        String ryLzrq = lzrqMap.get(id);
                        if (StringUtils.isNotEmpty(ryZt) && new BigDecimal(ryZt).compareTo(new BigDecimal("5")) != 0) {
                            ryLzrq = null;
                        }
                        for (String dateTemp : allDates) {
                            String groupId = kqGroupMemberComInfo.getKQGroupId(id, dateTemp);
                            if (StringUtils.isEmpty(groupId)) {
                                continue;
                            }
                            Set<String> strings = groupIdRqMap.get(groupId);
                            if ((!CollectionUtils.isEmpty(strings)) && strings.contains(dateTemp)) {
                                if (StringUtils.isEmpty(ryRzrq) || LocalDate.parse(dateTemp, formatterRq).isBefore(LocalDate.parse(ryRzrq, formatterRq))) {
                                    continue;
                                }
                                if (StringUtils.isNotEmpty(ryLzrq) && LocalDate.parse(dateTemp, formatterRq).isAfter(LocalDate.parse(ryLzrq, formatterRq))) {
                                    continue;
                                }
                                cnt = cnt + 1;
                            }
                        }
                        fieldValue = String.valueOf(cnt);
                    } else if (fieldName.equals("qyjjrts")) {  // 全月节假日天数（不考虑入离职）
                        int cnt = 0;
                        String groupId = "";
                        for (String dateTemp : allDates) {
                            groupId = kqGroupMemberComInfo.getKQGroupId(id, dateTemp);
                            if (StringUtils.isNotEmpty(groupId)) {
                                break;
                            }
                        }
                        if (StringUtils.isNotEmpty(groupId)) {
                            Set<String> strings = groupIdRqMap.get(groupId);
                            for (String dateTemp : allDates) {
                                if ((!CollectionUtils.isEmpty(strings)) && strings.contains(dateTemp)) {
                                    cnt = cnt + 1;
                                }
                            }
                        }
                        fieldValue = String.valueOf(cnt);
                    } else if (fieldName.equals("bkcsqz")) {  // 补卡次数
                        Integer cnt = bdkMap.get(id);
                        if (cnt == null) {
                            cnt = 0;
                        }
                        fieldValue = String.valueOf(cnt);
                    } else if (fieldName.equals("dyzhyggzr")) {  // 当月最后一个工作日日期
//						User user = new User(Integer.parseInt(id));
//						manager.setUser(user);
                        for (int i = allDates.size() - 1; i >= 0; i--) {
                            String rqTemp = allDates.get(i);
                            String groupId = kqGroupMemberComInfo.getKQGroupId(id, rqTemp);
                            if (StringUtils.isEmpty(groupId)) {
                                continue;
                            }
                            if (!CollectionUtils.isEmpty(groupIdSet)) {
                                Set<String> holidaySet = holidaySetMap.get(groupId);
                                Set<String> dpgzrdaySet = dpgzrdaySetMap.get(groupId);
                                if (CollectionUtils.isEmpty(holidaySet) || !holidaySet.contains(rqTemp)) {
                                    if ((!CollectionUtils.isEmpty(dpgzrdaySet)) && dpgzrdaySet.contains(rqTemp)) {
                                        fieldValue = rqTemp;
                                        break;
                                    } else {
                                        DayOfWeek dayOfWeek = LocalDate.parse(rqTemp).getDayOfWeek();
                                        if (dayOfWeek != DayOfWeek.SATURDAY && dayOfWeek != DayOfWeek.SUNDAY) {
                                            fieldValue = rqTemp;
                                            break;
                                        }
                                    }
                                }
                            } else {
                                DayOfWeek dayOfWeek = LocalDate.parse(rqTemp).getDayOfWeek();
                                if (dayOfWeek != DayOfWeek.SATURDAY && dayOfWeek != DayOfWeek.SUNDAY) {
                                    fieldValue = rqTemp;
                                    break;
                                }
                            }
                        }
                    } else if (fieldName.equals("sjycqss") || fieldName.equals("sjdksc")) {
                        fieldValue = Util.null2String(rs.getString(fieldName));
                    } else {
                        fieldValue = Util.null2String(rs.getString(fieldName));
                        if (Util.null2String(kqReportFieldComInfo.getUnittype()).length() > 0) {
                            if (fieldValue.length() == 0) {
                                fieldValue = "0";
                            } else {
                                if (kqReportFieldComInfo.getUnittype().equals("2")) {
                                    fieldValue = KQDurationCalculatorUtil.getDurationRound(("" + (Util.getDoubleValue(fieldValue) / 60.0)));
                                }
                            }
                        }
                    }
                    data.put(fieldName, fieldValue);
                }

                //请假
                List<Map<String, Object>> allLeaveRules = kqLeaveRulesBiz.getAllLeaveRules();
                if (("1").equals(leavetype_is_on)) {
                    KQLeaveRulesComInfo kqLeaveRulesComInfo = new KQLeaveRulesComInfo();
                    for (int i = allLeaveRules.size() - 1; i >= 0; i--) {
                        Map<String, Object> leaveRule = allLeaveRules.get(i);
                        String leaveId = Util.null2String(leaveRule.get("id"));
                        boolean flag = KQLeaveRulesBiz.filterSubLeaveRule(kqLeaveRulesComInfo, user, leaveId);
                        if (flag) {
                            allLeaveRules.remove(i);
                        }
                    }
                }
                Map<String, Object> leaveRule = null;
                for (int i = 0; allLeaveRules != null && i < allLeaveRules.size(); i++) {
                    leaveRule = (Map<String, Object>) allLeaveRules.get(i);
                    String flowType = Util.null2String("leaveType_" + leaveRule.get("id"));
                    String leaveData = Util.null2String(flowData.get(id + "|" + flowType));
                    String flowLeaveBackType = Util.null2String("leavebackType_" + leaveRule.get("id"));
                    String leavebackData = Util.null2s(Util.null2String(flowData.get(id + "|" + flowLeaveBackType)), "0.0");
                    String b_flowLeaveData = "";
                    String flowLeaveData = "";
                    try {
                        //以防止出现精度问题
                        if (leaveData.length() == 0) {
                            leaveData = "0.0";
                        }
                        if (leavebackData.length() == 0) {
                            leavebackData = "0.0";
                        }
                        BigDecimal b_leaveData = new BigDecimal(leaveData);
                        BigDecimal b_leavebackData = new BigDecimal(leavebackData);
                        b_flowLeaveData = b_leaveData.subtract(b_leavebackData).toString();
                        if (Util.getDoubleValue(b_flowLeaveData, -1) < 0) {
                            b_flowLeaveData = "0.0";
                        }
                    } catch (Exception e) {
                        writeLog("GetKQReportCmd:leaveData" + leaveData + ":leavebackData:" + leavebackData + ":" + e);
                    }

                    //考虑下冻结的数据
                    if (b_flowLeaveData.length() > 0) {
                        flowLeaveData = KQDurationCalculatorUtil.getDurationRound(b_flowLeaveData);
                    } else {
                        flowLeaveData = KQDurationCalculatorUtil.getDurationRound(Util.null2String(Util.getDoubleValue(leaveData, 0.0) - Util.getDoubleValue(leavebackData, 0.0)));
                    }
                    data.put(flowType, flowLeaveData);
                }

                Map<String, Object> amData = Maps.newHashMap();
                Map<String, Object> pmData = Maps.newHashMap();
                if (isKqReportHalfOpen) {
                    amData.putAll(data);
                    pmData.putAll(data);
                }

                Map<String, Object> detialDatas = kqReportBiz.getDetialDatas(id, fromDate, toDate, user, flowData, false, uintType, show_card_source);
//        new KQLog().info("id:"+id+":detialDatas:"+detialDatas);
                String user_endDate = Util.null2String(resourceComInfo.getEndDate(id));
                String user_status = Util.null2String(resourceComInfo.getStatus(id));
                String begindate = Util.null2String(resourceComInfo.getCreatedate(id)).trim();
                String companyStartDate = Util.null2String(resourceComInfo.getCompanyStartDate(id)).trim();
                if (companyStartDate.length() != 10) {
                    companyStartDate = "";
                }
                if (companyStartDate.length() > 0 && companyStartDate.indexOf("-") > 0) {
                    begindate = companyStartDate;
                }
                isEnd = false;
                for (String date = fromDate; !isEnd; ) {
                    if (date.equals(toDate)) isEnd = true;
                    if (DateUtil.compDate(today, date) > 0) {
                        if (isKqReportHalfOpen) {
                            amData.put(date, "");
                            pmData.put(date, "");
                        } else {
                            data.put(date, "");
                        }
                    } else {
                        if (!(user_status.equals("0") || user_status.equals("1") || user_status.equals("2") || user_status.equals("3"))) {
                            //其他状态
                            if (user_endDate.length() > 0 && date.compareTo(user_endDate) > 0) {//人员合同结束日期之后
                                if (isKqReportHalfOpen) {
                                    amData.put(date, "");
                                    pmData.put(date, "");
                                } else {
                                    data.put(date, "");
                                }
                                cal.setTime(DateUtil.parseToDate(date));
                                date = DateUtil.getDate(cal.getTime(), 1);
                                continue;
                            }
                        }
                        if (begindate.length() > 0 && date.compareTo(begindate) < 0) {//人员入职日期前无需计算考勤，如果没有入职日期，已创建日期为准
                            if (isKqReportHalfOpen) {
                                amData.put(date, "");
                                pmData.put(date, "");
                            } else {
                                data.put(date, "");
                            }
                            cal.setTime(DateUtil.parseToDate(date));
                            date = DateUtil.getDate(cal.getTime(), 1);
                            continue;
                        }

                        if (isKqReportHalfOpen) {
                            Map<String, Object> tmpData = Maps.newHashMap();
                            Object tmpValue = null;
                            if (detialDatas.get(id + "|" + date) == null) {
                                tmpValue = SystemEnv.getHtmlLabelName(26593, user.getLanguage());
                                data.put(date, tmpValue);
                                amData.put(date, tmpValue);
                                pmData.put(date, tmpValue);
                            } else {
                                tmpData = (Map<String, Object>) detialDatas.get(id + "|" + date);
                                data.put(date, tmpData);
                                if (tmpData != null) {
                                    tmpData.put("text", Util.null2String(tmpData.get("textAM")));

                                    Map<String, Object> amTmpData = Maps.newHashMap();
                                    amTmpData.put("text", Util.null2String(tmpData.get("textAM")));
                                    Map<String, Object> pmTmpData = Maps.newHashMap();
                                    pmTmpData.put("text", Util.null2String(tmpData.get("textPM")));
                                    amData.put(date, amTmpData);
                                    pmData.put(date, pmTmpData);
                                }
                            }
                        } else {
                            data.put(date, detialDatas.get(id + "|" + date) == null ? SystemEnv.getHtmlLabelName(26593, user.getLanguage()) : detialDatas.get(id + "|" + date));
                        }
                    }
                    cal.setTime(DateUtil.parseToDate(date));
                    date = DateUtil.getDate(cal.getTime(), 1);
                }
                if (isKqReportHalfOpen) {
                    amData.put("rowSpan", "2");
                    pmData.put("rowSpan", "0");
                    datas.add(amData);
                    datas.add(pmData);
                } else {
                    datas.add(data);
                }
            }

            List<Object> lsHolidays = KQHolidaySetBiz.getHolidaySetListByScope("" + user.getUID(), fromDate, toDate);
            retmap.put("holidays", lsHolidays);

            retmap.put("columns", columns);
            retmap.put("datas", datas);
            if (isKqReportHalfOpen) {
                retmap.put("pagesize", pageSize * 2);
                retmap.put("count", count * 2);
            } else {
                retmap.put("pagesize", pageSize);
                retmap.put("count", count);
            }
            retmap.put("pageindex", pageIndex);
            retmap.put("pagecount", pageCount);
            retmap.put("ishavepre", isHavePre);
            retmap.put("ishavenext", isHaveNext);
            retmap.put("isKqReportHalfOpen", isKqReportHalfOpen);
        } catch (Exception e) {
            writeLog("GetKQReportCmd error:" + e);
        }
        return retmap;
    }

    private void generateWorkDays(String fromDate, String toDate, RecordSet rs) {
        RecordSet rs2 = new RecordSet();
        String sql = "select distinct groupid from KQ_HolidaySet";
        String workSql;
        rs.executeQuery(sql);
        while (rs.next()) {
            String groupid = rs.getString("groupid");
            workSql = "\n" +
                    "SELECT \n" +
                    "    COUNT(*) AS workdaysbymonth\n" +
                    "FROM \n" +
                    "\n" +
                    "(\n" +
                    "    SELECT \n" +
                    "        d.dateStr,\n" +
                    "        d.isWeekend,\n" +
                    "        b.changeType,b.groupId\n" +
                    "    FROM (\n" +
                    "        SELECT \n" +
                    "            CONVERT(VARCHAR(10), DATEADD(DAY, number, DATEFROMPARTS(YEAR(GETDATE()), 1, 1)), 120) AS dateStr,\n" +
                    "            CASE \n" +
                    "                WHEN DATEPART(WEEKDAY, DATEADD(DAY, number, DATEFROMPARTS(YEAR(GETDATE()), 1, 1))) IN (1, 7) \n" +
                    "                THEN 1 \n" +
                    "                ELSE 0 \n" +
                    "            END AS isWeekend\n" +
                    "        FROM master.dbo.spt_values\n" +
                    "        WHERE type = 'P'\n" +
                    "            AND number < DATEDIFF(DAY, DATEFROMPARTS(YEAR(GETDATE()), 1, 1), DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1))\n" +
                    "    ) d\n" +
                    "    LEFT JOIN KQ_HolidaySet b ON b.holidayDate = d.dateStr and b.groupId=\n" + groupid +
                    "    WHERE d.dateStr BETWEEN  '" + fromDate + "' AND  '" + toDate + "'\n" +
                    ") t\n" +
                    "WHERE (\n" +
                    "    (isWeekend = 0 AND (changeType IS NULL OR changeType = 2))\n" +
                    "    OR \n" +
                    "    (isWeekend = 1 AND changeType = 2)\n" +
                    ")";
            rs2.executeQuery(workSql);
            if (rs2.next()) {
                String workdayByMonth = rs2.getString("workdaysbymonth");
                rs2.executeUpdate("update uf_custom_holiday set workdaysbymonth=? where groupid=?", workdayByMonth, groupid);
            }
        }

    }

    private Map<String, Object> getChildColumnsInfo(String parentid, User user) {
        Map<String, Object> returnMap = new HashMap<>();
        List<Object> lsChildColumns = new ArrayList<>();
        Map column = null;
        int sumChildColumnWidth = 0;
        KQSettingsComInfo kqSettingsComInfo = new KQSettingsComInfo();
        String leavetype_is_on = Util.null2String(kqSettingsComInfo.getMain_val("leavetype_is_on"), "0");
        if (parentid.equals("attendanceSerial")) {//考勤班次
            KQShiftManagementComInfo kqShiftManagementComInfo = new KQShiftManagementComInfo();
            JSONObject jsonObj = JSON.parseObject(Util.null2String(params.get("data")));
            List<String> serialIds = null;
            if (Util.null2String(jsonObj.get("attendanceSerial")).length() > 0) {
                serialIds = Util.splitString2List(Util.null2String(jsonObj.get("attendanceSerial")), ",");
            }
            for (int i = 0; serialIds != null && i < serialIds.size(); i++) {
                column = new HashMap();
                column.put("title", kqShiftManagementComInfo.getSerial(serialIds.get(i)));
                column.put("unit", "");
                column.put("width", 65);
                column.put("dataIndex", serialIds.get(i));
                column.put("key", serialIds.get(i));
                column.put("rowSpan", 2);
                column.put("colSpan", 1);
                sumChildColumnWidth += 65;
                lsChildColumns.add(column);
            }
        } else if (parentid.equals("leave")) {
            KQLeaveRulesBiz kqLeaveRulesBiz = new KQLeaveRulesBiz();
            List<Map<String, Object>> leaveRules = kqLeaveRulesBiz.getAllLeaveRules();
            if (("1").equals(leavetype_is_on)) {
                KQLeaveRulesComInfo kqLeaveRulesComInfo = new KQLeaveRulesComInfo();
                for (int i = leaveRules.size() - 1; i >= 0; i--) {
                    Map<String, Object> leaveRule = leaveRules.get(i);
                    String leaveId = Util.null2String(leaveRule.get("id"));
                    boolean flag = kqLeaveRulesBiz.filterSubLeaveRule(kqLeaveRulesComInfo, user, leaveId);
                    if (flag) {
                        leaveRules.remove(i);
                    }
                }
            }
            for (int i = 0; leaveRules != null && i < leaveRules.size(); i++) {
                Map<String, Object> leaveRule = leaveRules.get(i);
                String id = "leaveType_" + Util.null2String(leaveRule.get("id"));
                String name = Util.null2String(leaveRule.get("name"));
                String unitType = Util.null2String(leaveRule.get("unitType"));
                column = new HashMap();
                column.put("title", name);
                column.put("unit", KQUnitBiz.isLeaveHour(unitType) ? SystemEnv.getHtmlLabelName(391, user.getLanguage()) : SystemEnv.getHtmlLabelName(1925, user.getLanguage()));
                column.put("width", 65);
                column.put("dataIndex", id);
                column.put("key", id);
                column.put("rowSpan", 2);
                column.put("colSpan", 1);
                column.put("showDetial", "1");
                sumChildColumnWidth += 65;
                lsChildColumns.add(column);
            }
        } else if (parentid.equals("overtime")) {
            String[] overtimeChild = {"overtime_nonleave", "overtime_4leave", "overtimeTotal"};
            for (int i = 0; i < overtimeChild.length; i++) {
                String id = overtimeChild[i];
                column = new HashMap();
                String fieldlabel = "";
                column.put("unit", "");
                if ("overtime_nonleave".equalsIgnoreCase(id)) {
                    fieldlabel = "125805";
                } else if ("overtime_4leave".equalsIgnoreCase(id)) {
                    fieldlabel = "125804";
                } else {
                    fieldlabel = "523";
                    column.put("showDetial", "1");
                    String unitType = (KQOvertimeRulesBiz.getMinimumUnit() == 3 || KQOvertimeRulesBiz.getMinimumUnit() == 5 || KQOvertimeRulesBiz.getMinimumUnit() == 6) ? "2" : "1";
                    String unitTypeName = "";
                    if (Util.null2String(unitType).length() > 0) {
                        if (unitType.equals("1")) {
                            unitTypeName = SystemEnv.getHtmlLabelName(1925, user.getLanguage());
                        } else if (unitType.equals("2")) {
                            unitTypeName = SystemEnv.getHtmlLabelName(391, user.getLanguage());
                        } else if (unitType.equals("3")) {
                            unitTypeName = SystemEnv.getHtmlLabelName(18083, user.getLanguage());
                        }
                    }
                    column.put("unit", unitTypeName);
                }
                column.put("title", SystemEnv.getHtmlLabelNames(fieldlabel, user.getLanguage()));
                column.put("dataIndex", id);
                column.put("key", id);
                column.put("rowSpan", 1);
                Map<String, Object> mapChildColumnInfo = getChildColumnsInfo(id, user);
                int childWidth = 65;
                List<Object> childColumns = (List<Object>) mapChildColumnInfo.get("childColumns");
                if (childColumns.size() > 0) {//跨列width取子列的width
                    column.put("children", childColumns);
                    childWidth = Util.getIntValue(Util.null2String(mapChildColumnInfo.get("sumChildColumnWidth")), 65);
                }
                column.put("width", childWidth + "");
                sumChildColumnWidth += childWidth;
                lsChildColumns.add(column);
            }
        } else {
            KQReportFieldComInfo kqReportFieldComInfo = new KQReportFieldComInfo();
            while (kqReportFieldComInfo.next()) {
                if (kqReportFieldComInfo.getParentid().equals(parentid)) {
                    if (!kqReportFieldComInfo.getReportType().equals("month")) continue;
                    column = new HashMap();
                    column.put("title", SystemEnv.getHtmlLabelNames(kqReportFieldComInfo.getFieldlabel(), user.getLanguage()));
                    column.put("unit", KQReportBiz.getUnitType(kqReportFieldComInfo, user));
                    column.put("width", Util.getIntValue(kqReportFieldComInfo.getWidth()));
                    column.put("dataIndex", kqReportFieldComInfo.getFieldname());
                    column.put("key", kqReportFieldComInfo.getFieldname());
                    column.put("rowSpan", 1);
                    column.put("colSpan", 1);
                    column.put("showDetial", kqReportFieldComInfo.getShowDetial());
                    sumChildColumnWidth += Util.getIntValue(kqReportFieldComInfo.getWidth());
                    lsChildColumns.add(column);
                }
            }
        }
        returnMap.put("childColumns", lsChildColumns);
        returnMap.put("sumChildColumnWidth", sumChildColumnWidth);
        return returnMap;
    }

    private List<Object> getCascadeKeyColumnsInfo(String cascadeKey, User user) {
        List<Object> lsChildColumns = new ArrayList<>();
        if (Util.null2String(cascadeKey).length() == 0) {
            return lsChildColumns;
        }
        Map<String, Object> column = null;
        List<String> lsCascadeKey = Util.splitString2List(cascadeKey, ",");
        KQReportFieldComInfo kqReportFieldComInfo = new KQReportFieldComInfo();
        for (int i = 0; i < lsCascadeKey.size(); i++) {
            kqReportFieldComInfo.setTofirstRow();
            while (kqReportFieldComInfo.next()) {
                if (!kqReportFieldComInfo.getReportType().equals("month")) continue;
                if (kqReportFieldComInfo.getFieldname().equals(lsCascadeKey.get(i))) {
                    column = new HashMap();
                    column.put("title", SystemEnv.getHtmlLabelNames(kqReportFieldComInfo.getFieldlabel(), user.getLanguage()));
                    column.put("unit", KQReportBiz.getUnitType(kqReportFieldComInfo, user));
                    column.put("width", Util.getIntValue(kqReportFieldComInfo.getWidth()));
                    column.put("dataIndex", kqReportFieldComInfo.getFieldname());
                    column.put("key", kqReportFieldComInfo.getFieldname());
                    column.put("rowSpan", 1);
                    column.put("colSpan", 1);
                    column.put("showDetial", kqReportFieldComInfo.getShowDetial());
                    column.put("isSystem", kqReportFieldComInfo.getIsSystem());
                    lsChildColumns.add(column);
                }
            }
        }
        return lsChildColumns;
    }

    /**
     * * 关联流程计算加班时长  加班时长减去调休
     */
    public double getOvertimeCount(String userid, String fromDate, String toDate, double leaveTime, String leaveType) {
        BigDecimal bigDecimal = new BigDecimal(Double.toString(leaveTime));

        KQWorkTime kqWorkTime = new KQWorkTime();
        String propValue = Util.null2String(Prop.getPropValue("Others", "wfTableName"));
        if ("".equals(propValue)) {
            return leaveTime;
        }
        String sql = " select * from " + propValue + " where jbksrq >= ? and jbksrq >= ? and sqr = ?";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql, fromDate, toDate, userid);

        while (rs.next()) {
            String workdate = rs.getString("jbksrq");
            boolean workDay = kqWorkTime.isWorkDay(userid, workdate);
            boolean isHoliday = KQHolidaySetBiz.isHoliday(userid, workdate);
            double dxsc = rs.getDouble("dxsc");
            dxsc = (double) Math.round(dxsc * 100) / 100;
            BigDecimal bigDecimalDxsc = new BigDecimal(Double.toString(dxsc));
            if (workDay) {
                if ("1".equals(leaveType)) {
                    bigDecimal.subtract(bigDecimalDxsc);
                }
            } else if (isHoliday) {
                if ("2".equals(leaveType)) {
                    bigDecimal.subtract(bigDecimalDxsc);
                }

            } else {
                if ("3".equals(leaveType)) {
                    bigDecimal.subtract(bigDecimalDxsc);
                }

            }
        }
        return bigDecimal.doubleValue();
    }

    @Override
    public BizLogContext getLogContext() {
        return null;
    }

    public static void main(String[] args) {
        String s1 = String.format("%.2f", 1.2);
        System.out.println(s1);

    }

}
